bm-site

Appearance

7种 MySQL亿级/千万级大表快速删除数据方案：锁影响与性能分析 ​

202512062334063aeb1a5ca.png

以下是MySQL大表删除方案，重点分析每种方案的锁定影响、执行时间和系统负载，从最优到次优排序：

1. TRUNCATE TABLE（全表清空） ​

plsql
TRUNCATE TABLE huge_table;

锁定分析：

  • 锁定程度：表级锁，阻塞所有读写
  • 锁定时间：极短（通常几秒）
  • 执行效率：即时操作，与表大小关系不大
  • 系统影响：最小，几乎不产生redo/undo日志

使用场景：

  • 需要删除表中全部数据且保留表结构
  • 不需要事务回滚能力
  • 可接受短暂的表不可用

注意事项：无法指定条件删除，是DDL操作，不可回滚

2. DROP PARTITION（分区删除） ​

plsql
ALTER TABLE huge_table DROP PARTITION p_202301;

锁定分析：

  • 锁定程度：表元数据锁，主要影响DDL操作
  • 锁定时间：极短（通常几秒，即使分区包含数千万行）
  • 执行效率：几乎与分区数据量无关，主要是文件系统操作
  • 系统影响：最小，不生成事务日志

使用场景：

  • 表已经是分区表
  • 删除条件与分区键完全匹配
  • 例如：按月分区的表删除整月数据

注意事项：

  • 要求表已经是分区表
  • 只能按分区边界删除，不能部分删除分区内容

3. 快速表重建法（零锁/最小锁方案） ​

plsql
-- 1. 创建新表（不锁原表）  
CREATE TABLE huge_table_new LIKE huge_table;  

-- 2. 批量插入要保留的数据（不锁定原表读取）  
INSERT INTO huge_table_new  
SELECT * FROM huge_table WHERE keep_condition LIMIT 1000000;  
-- 继续更多批次...  

-- 3. 创建索引（不锁原表）  
ALTER TABLE huge_table_new ADD INDEX...  

-- 4. 原子切换表（极短时间锁定）  
RENAME TABLE huge_table TO huge_table_old,  
             huge_table_new TO huge_table;

锁定分析：

  • 锁定程度：仅在RENAME期间短暂锁定（毫秒级）
  • 锁定时间：几乎可忽略
  • 执行效率：高（只复制需要保留的数据）
  • 系统影响：中等（需要额外存储空间）

使用场景：

  • 删除比例大于50%的数据
  • 要求几乎零停机时间
  • 可以接受额外的磁盘空间使用

4. 普通批量删除（高系统负载） ​

plsql
-- 基本批量删除循环  
DELETE FROM huge_table   
WHERE delete_condition   
LIMIT 10000;

锁定分析：

  • 锁定程度：行级锁，可能导致锁升级
  • 锁定时间：每批次短时间锁定，总体较长
  • 执行效率：低（需要扫描所有数据，记录日志）
  • 系统影响：高（大量I/O，redo/undo日志，索引维护）

使用场景：

  • 必须在线删除且无法使用其他方案
  • 删除比例较小（<30%）
  • 可以接受系统性能降低

优化版本：

plsql
-- 批量删除优化版  
CREATE PROCEDURE batch_delete_data(IN p_batch_size INT)  
BEGIN  
    DECLARE v_rows_affected INT;  
    DECLARE v_total_deleted INT DEFAULT 0;  
    DECLARE v_start_time DATETIME;  
    DECLARE v_end_time DATETIME;  
    
    SET v_start_time = NOW();  
    SET v_rows_affected = 1;  
    
    WHILE v_rows_affected > 0 DO  
        START TRANSACTION;  
        DELETE FROM huge_table   
        WHERE delete_condition   
        ORDER BY id  -- 确保按索引删除  
        LIMIT p_batch_size;  
        
        SET v_rows_affected = ROW_COUNT();  
        SET v_total_deleted = v_total_deleted + v_rows_affected;  
        
        COMMIT;  
        
        -- 允许其他事务执行  
        DO SLEEP(0.1);  
    END WHILE;  
    
    SET v_end_time = NOW();  
    
    SELECT   
        CONCAT('已删除 ', v_total_deleted, ' 行数据') AS Total_Deleted,  
        CONCAT('执行时间: ', TIMESTAMPDIFF(SECOND, v_start_time, v_end_time), ' 秒') AS Execution_Time;  
END;

5. 使用pt-archiver专业工具（低影响） ​

powershell
pt-archiver \
  --source h=localhost,D=database,t=huge_table,u=root,p=password \
  --where "create_time < '2023-01-01'" \
  --no-check-charset \
  --limit 1000 \
  --commit-each \
  --sleep 0.5 \
  --bulk-delete

锁定分析：

  • 锁定程度：极小（智能锁控制）
  • 锁定时间：每批次短暂锁定
  • 执行效率：中等（比普通删除更智能）
  • 系统影响：低（自动调节删除速度避免系统过载）

使用场景：

  • 需要在生产环境进行大批量删除
  • 需要精细控制删除过程
  • 优先考虑系统稳定性

6. 归档+删除组合策略（低锁复合方案） ​

plsql
-- 1. 先将要删除的数据归档（不锁表）  
CREATE TABLE archive_202301 AS   
SELECT * FROM huge_table WHERE date_column < '2023-02-01';  

-- 2. 再执行删除（可选多种删除策略）  
-- 例如批量删除  
DELETE FROM huge_table   
WHERE date_column < '2023-02-01'  
LIMIT 10000;

锁定分析：

  • 锁定程度：分两阶段，归档阶段几乎不锁
  • 锁定时间：可控
  • 执行效率：中等
  • 系统影响：中等（需要额外存储空间）

使用场景：

  • 需要保留历史数据副本
  • 可以先归档再删除
  • 强调数据安全性

7. 转换为分区表后删除（长期最优方案） ​

plsql
-- 使用pt-online-schema-change工具实现在线转换  
pt-online-schema-change \
  --alter="PARTITION BY RANGE (TO_DAYS(create_time)) (  
    PARTITION p_202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),  
    PARTITION p_202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),  
    PARTITION p_future VALUES LESS THAN MAXVALUE)" \
  --no-drop-old-table \
  h=localhost,u=root,p=password,D=database,t=huge_table \
  --execute  

-- 转换后，未来删除极快  
ALTER TABLE huge_table DROP PARTITION p_202301;

锁定分析：

  • 转换阶段：
    • 使用工具可实现几乎无锁转换
    • 锁定程度最小，但过程较长
  • 删除阶段：
    • 极小锁定（几秒钟）
    • 执行效率极高

使用场景：

  • 长期需要定期删除历史数据
  • 有运维窗口进行一次性转换
  • 需要最优的长期解决方案

方案对比表 ​

方案锁定程度执行时间(1亿行)系统影响主要限制适用场景
TRUNCATE高(表锁)1-10秒极低全表删除完全清空
DROP PARTITION低(元数据)1-10秒极低需分区表已分区表定期清理
表重建法极低1-8小时中等额外存储删除大比例数据
批量删除中(行锁)10-48小时高系统负载必须在线且无其他选择
pt-archiver低8-24小时低需额外工具生产环境安全删除
归档+删除中8-30小时中额外存储需保留历史数据
转换为分区低→极低转换:8-24h
之后删除:秒级		转换:中
之后:极低		初始投入长期数据管理

最佳实践建议 ​

  1. 短期快速删除：
    • 单次操作选择表重建法或pt-archiver工具
    • 已分区表直接使用DROP PARTITION
  2. 长期策略：
    • 投资将大表转换为分区表
    • 使用pt-online-schema-change工具实现低影响转换
    • 建立自动化分区管理流程
  3. 风险控制：
    • 始终在操作前备份
    • 先在测试环境验证
    • 监控复制延迟和系统资源
    • 在业务低峰期执行

每种方案都有其适用场景和权衡，选择时应考虑：业务可接受的停机时间、系统当前负载、操作紧急程度以及长期数据管理需求。通常，长期来看，分区表策略提供最佳的总体拥有成本和性能。

更新: 2025-04-21 20:45:20
原文: https://www.yuque.com/tulingzhouyu/db22bv/ddplb7o15cmmgxf5